package fu.capstone.hanoicityguide.dataaccess.itinerary;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import fu.capstone.hanoicityguide.R;
import fu.capstone.hanoicityguide.business.model.GeoCoordinate;
import fu.capstone.hanoicityguide.business.model.Itinerary;
import fu.capstone.hanoicityguide.business.model.Place;
import fu.capstone.hanoicityguide.dataaccess.base.BaseDAOImpl;
import fu.capstone.hanoicityguide.dataaccess.base.DatabaseHelper;

public class ItineraryDAOImp extends BaseDAOImpl implements ItineraryDAO {
	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	private Context context;

	public ItineraryDAOImp(Context context) {
		super(context);
		this.context = context;
	}

	@Override
	public ArrayList<Itinerary> listItinerary(String keyword, String sorter) {
		Cursor cursor = null;
		ArrayList<Itinerary> arrResult = new ArrayList<Itinerary>();
		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryItinerary = "SELECT Itinerary.[Id] , Itinerary.[ItineraryName] , "
					+ "Rating.[RatingValue] , "
					+ "AlbumImage.[IconPath] FROM Itinerary "
					+ "LEFT JOIN Rating ON Itinerary.[RatingId] = Rating.[Id] "
					+ "LEFT JOIN AlbumImage ON Itinerary.[AlbumImageId] = AlbumImage.[Id] ";

			// filter by keyword
			queryItinerary = queryItinerary
					+ " WHERE Itinerary.[ItineraryName] LIKE '%"
					+ keyword.trim() + "%' ";

			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[0])) {
				queryItinerary = queryItinerary
						+ "ORDER BY Rating.[RatingValue] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[1])) {
				queryItinerary = queryItinerary
						+ "ORDER BY Rating.[RatingValue] ASC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryItinerary = queryItinerary
						+ "ORDER BY Itinerary.[ItineraryName] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryItinerary = queryItinerary
						+ "ORDER BY Itinerary.[ItineraryName] ASC";
			} else {
			}

			cursor = database.rawQuery(queryItinerary, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						Itinerary itinerary = new Itinerary();
						itinerary.setId(cursor.getInt(cursor
								.getColumnIndex("Id")));
						itinerary.setItineraryName(cursor.getString(cursor
								.getColumnIndex("ItineraryName")));
						itinerary.setQuality(cursor.getInt(cursor
								.getColumnIndex("RatingValue")));
						itinerary.setIconPath(cursor.getString(cursor
								.getColumnIndex("IconPath")));

						arrResult.add(itinerary);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("listItinerary : Can't query DB", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return arrResult;
	}

	@Override
	public ArrayList<Place> browseItinerary(int itineraryId) {
		Cursor cursor = null;
		ArrayList<Place> arrResult = new ArrayList<Place>();
		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryItineraryDetail = "SELECT Place.[Id] , Place.[Name] , Category.[CategoryName],"
					+ "Rating.[RatingValue], AlbumImage.[IconPath], Geolocation.[Latitude], Geolocation.[Longitude], "
					+ "AlbumImage.[ImagePath1] , AlbumImage.[ImagePath2], AlbumImage.[ImagePath3], Type.[TypeName], "
					+ "AlbumImage.[ImagePath4], AlbumImage.[ImagePath5] FROM ItineraryDetail "
					+ "LEFT JOIN Place ON ItineraryDetail.[PlaceId] = Place.[Id] "
					+ "LEFT JOIN Geolocation ON Place.[GeoId] = Geolocation.[Id] "
					+ "LEFT JOIN AlbumImage ON Place.[AlbumImageId] = AlbumImage.[Id] "
					+ "LEFT JOIN Category ON Place.[CategoryId] = Category.[Id] "
					+ "LEFT JOIN Type ON Place.[TypeId] = Type.[Id] "
					+ "LEFT JOIN Rating ON Place.[RatingId] = Rating.[Id] "
					+ "WHERE ItineraryDetail.[ItineraryId] = '"
					+ itineraryId
					+ "'";

			cursor = database.rawQuery(queryItineraryDetail, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						Place place = new Place();
						ArrayList<String> arrImage = new ArrayList<String>();
						place.setId(Integer.parseInt(cursor.getString(cursor
								.getColumnIndex("Id"))));
						place.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						place.setCategory(cursor.getString(cursor
								.getColumnIndex("CategoryName")));
						place.setQuality(Integer.parseInt(cursor
								.getString(cursor.getColumnIndex("RatingValue"))));
						place.setIconImage(cursor.getString(cursor
								.getColumnIndex("IconPath")));
						place.setType(cursor.getString(cursor
								.getColumnIndex("TypeName")));

						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath1")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath2")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath3")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath4")));
						arrImage.add(cursor.getString(cursor
								.getColumnIndex("ImagePath5")));

						GeoCoordinate geo = new GeoCoordinate();
						geo.setLatitude(Double.parseDouble(cursor
								.getString(cursor.getColumnIndex("Latitude"))));
						geo.setLongitude(Double.parseDouble(cursor
								.getString(cursor.getColumnIndex("Longitude"))));
						place.setGeo(geo);

						place.setArrImage(arrImage);
						arrResult.add(place);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("listItineraryDetail : Can't query DB", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return arrResult;
	}

}
